# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from xlwt import *
import xlrd
import sys
from xlutils.copy import copy
import os

reload(sys)
sys.setdefaultencoding('utf8')

"""
author：zhangqing
function: 数据初始化对比：cvs1.0与cvs2.0 人物列表职业生涯对比（公司全称、职位）
"""

def searchCvs1PersionManager(persionIdList):
    db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)

    cursor = db.cursor()

    print("persionIdList==",persionIdList)
    # persionId1Str = ''
    #循环获取1.0库中的persionId，找到符合条件id后去dc_manager表中获取排名第一的职业生涯信息
    for index in range(len(persionIdList)):
        persionId1 = persionIdList[index].get(u'persionId1')
        # persionId1Str += ",'"+persionId1+"'"
        persionId2 = persionIdList[index].get(u'persionId2')
        sql = "SELECT personage.id,personage.cn_name,personage.en_name,b.context_id,b.context_type, b.title, b.career_order,b.ext1 AS beizhu, b.ext2 AS company_full_name1 " \
          "from" \
          " dc_personage personage," \
          "(select manager.* FROM (SELECT manager.personage_id,MIN(manager.career_order) career_order " \
          "from " \
          "dc_manager manager  WHERE manager.`status`='1' AND manager.context_type IN(2,3,4,5) GROUP BY manager.personage_id) temp " \
          "left join dc_manager manager  on temp.personage_id = manager.personage_id and temp.career_order = manager.career_order) b " \
          "WHERE personage.id = b.personage_id AND personage.`status`='1'  AND personage.id='%s'"%persionId1

        cursor.execute(sql)
        managerResults1 = cursor.fetchall()
        fields = cursor.description
        if len(managerResults1)==0:
           print("persionId1=="+persionId1+"===>manager中无此条数据")
           continue

        context_type = managerResults1[0].get(u"context_type")
        context_id = managerResults1[0].get(u"context_id")
        #context_type == 2 代表机构，context_type == 3 代表LP，context_type == 5 代表基金，
        if context_type == 2:
           contextsql = "SELECT org.name as fullName FROM dc_organization org WHERE id='%s'"%context_id
        if context_type == 3:
           contextsql = "SELECT lp.name as fullName FROM dc_lp lp WHERE id='%s'"%context_id
        if context_type == 5:
           contextsql = "SELECT fund.cn_name as fullName FROM dc_fund fund WHERE id='%s'"%context_id
        cursor.execute(contextsql)
        contextResults1 = cursor.fetchall()
        if len(contextResults1)>0:
            company_full_name1 = contextResults1[0].get(u"fullName")
            title1 = contextResults1[0].get(u"title")
            searchCvs2PersionManager(managerResults1,fields,persionId2,company_full_name1,title1)

    db.close()

def searchCvs2PersionManager(managerResults1,fields,persionId2,company_full_name1,title1):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "cvs_prod", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT person_id AS id ,cn_name,en_name,company_full_name AS context_id, position AS title,company_type AS context_type,null AS career_order ,enable_search AS beizhu FROM view_person WHERE person_id='%s'"%persionId2
    cursor.execute(sql)
    viewPersionResults2 = cursor.fetchall()
    company_full_name = viewPersionResults2[0].get(u"context_id")
    title = viewPersionResults2[0].get(u"title")
    flag = company_full_name1 != company_full_name and title1 != title
    if  flag:
       managerResults1[0].update(beizhu=u'cvs1和2中viewPersion 任职(在职)公司全称\职位 不完全匹配，此为cvs1中数据')
       managerResults1[0].update(company_full_name1=company_full_name1)
       writeExcel(managerResults1[0],fields)
       viewPersionResults2[0].update(beizhu=u'cvs1和2中viewPersion 任职(在职)公司全称\职位 不完全匹配，此为cvs2中数据')
       writeExcel(viewPersionResults2[0],fields)

    db.close()

def writeExcel(results,fields):

    fileName = 'dataViewPersonManger.xlsx'
    p1 = os.path.exists(fileName)

    if p1:
        f = xlrd.open_workbook(fileName, formatting_info=True)
        sheet = f.sheet_by_index(0)
        rowNum = sheet.nrows
        newbook =copy(f)
        newsheet = newbook.get_sheet(0)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print rowNum,j,x
            newsheet.write(rowNum,j,x)

        newbook.save(fileName)

    else:

        workbook  = Workbook(encoding = 'utf-8')
        table = workbook .add_sheet('data')

        for field in range(0,len(fields)):
            table.write(0,field,fields[field][0])

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print 1,j,x
            table.write(1,j,x)

        workbook .save(fileName)
